package com.pf.export;

import com.google.common.collect.Lists;
import com.pf.utils.ExportExcelUtils;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * 导出账单excel
 * <p>
 * 1.含有表头样式，数据行样式
 * 2.超链接
 * 3.下拉列表
 * 4.不可修改灰色背景
 *
 * @author PanFei
 * @version 1.0.0
 * @createTime 2023/3/4 17:17
 */
public class BillDemo {

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Builder
    public static class BillDTO {
        private String billNo;
        private String billType;
        private BigDecimal amount;
        private Date billDate;
        private String remark;
    }

    public static void main(String[] args) throws Exception {
        // 1.准则表头和数据行数据
        List<String> excelHeadTitles = Lists.newArrayList("ID", "账单类型", "金额", "账单日期", "备注");
        BillDTO b1 = new BillDTO("bill20230304000001", "收入", new BigDecimal("3000"), new Date(), "备注1");
        BillDTO b2 = new BillDTO("bill20230304000002", "支出", new BigDecimal("1000"), new Date(), "备注2");
        BillDTO b3 = new BillDTO("bill20230304000003", "支出", new BigDecimal("1000"), new Date(), "备注3");
        List<BillDTO> billList = Lists.newArrayList(b1, b2, b3);

        // 2.创建excel工作薄对象，创建表头样式和数据行样式
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFCellStyle headCellStyle = ExportExcelUtils.getHeadCellStyle(workbook);
        XSSFCellStyle dataCellStyle = ExportExcelUtils.getDataCellStyle(workbook);
        XSSFCellStyle dataLinkCellStyle = ExportExcelUtils.getDataLinkCellStyle(workbook);
        XSSFCellStyle dataGreyCellStyle = ExportExcelUtils.getDataGreyCellStyle(workbook);

        // 金额的单元格格式， #,##0
        XSSFCreationHelper creationHelper = workbook.getCreationHelper();
        XSSFCellStyle amountCellStyle = ExportExcelUtils.getDataCellStyle(workbook);
        // 因为数字才可以设置为  #,##0 ，所以输出金额时，需将 BigDecimal类型转为Double类型
        amountCellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0"));

        // 日期的格式，yyyy-mm-dd
        XSSFCellStyle dateCellStyle = ExportExcelUtils.getDataCellStyle(workbook);
        dateCellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-mm-dd"));

        XSSFSheet sheet = workbook.createSheet("账单明细");
        // 设置默宽度：25
        sheet.setDefaultColumnWidth(25);
        // 3.创建表头行
        int rowIndex = 0;
        XSSFRow row = sheet.createRow(rowIndex);
        row.setHeightInPoints(30);
        for (int i = 0; i < excelHeadTitles.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeadTitles.get(i));
            cell.setCellStyle(headCellStyle);
            // 设置列宽，自适应
//            sheet.autoSizeColumn(i);
        }

        // 4.创建数据行
        for (int i = 0; i < billList.size(); i++) {
            rowIndex = ++rowIndex;
            BillDTO bill = billList.get(i);
            XSSFRow dataRow = sheet.createRow(rowIndex);
            dataRow.setHeightInPoints(30);
            // 创建【ID】列
            XSSFCell idCell = dataRow.createCell(0);
            idCell.setCellValue(bill.getBillNo());
            idCell.setCellStyle(dataLinkCellStyle);

            // 创建【账单类型】列
            XSSFCell billTypeCell = dataRow.createCell(1);
            billTypeCell.setCellValue(bill.getBillType());
            billTypeCell.setCellStyle(dataGreyCellStyle);

            // 创建【金额】列
            XSSFCell amountCell = dataRow.createCell(2);
            amountCell.setCellValue(bill.getAmount().doubleValue());
            amountCell.setCellStyle(amountCellStyle);

            // 创建【账单日期】列
            XSSFCell amountDateCell = dataRow.createCell(3);
            amountDateCell.setCellValue(bill.getBillDate());
            amountDateCell.setCellStyle(dateCellStyle);

            // 创建【备注】列
            XSSFCell remarkCell = dataRow.createCell(4);
            remarkCell.setCellValue(bill.getRemark());
            remarkCell.setCellStyle(dataCellStyle);
        }

        // 为【账单类型】单元格设置下拉列表
        ExportExcelUtils.createBoundedDropDownList(sheet, 1, Lists.newArrayList("收入", "支出"));

        // 5.输出账单excel
        workbook.write(new FileOutputStream("D:/test/billDetail.xlsx"));
        System.out.println(">>>> 导出成功...");
    }
}
